﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using RN.ERP.Entity;
using RN.ERP.Entity.QD;
using RN.ERP.Entity.ResultModels;
using Fly.Log;
using System.Data.Entity;
using System.Data.SqlClient;

namespace RN.ERP.DAL
{
    public class EnterpriseDAL
    {
        QD_Context dbContext = new QD_Context();

        #region 企业信息
        /// <summary>
        /// 公共:查询企业信息列表
        /// </summary>
        /// <param name="pageCount"></param>
        /// <param name="pageSize"></param>
        /// <param name="conditions"></param>
        /// <returns></returns>
        public List<Tb_Corp_CompanyInfo> GetCorpInfos(string condition)
        {
            var list = new List<Tb_Corp_CompanyInfo>();
            string sql = "SELECT * From tb_Corp_CompanyInfo C left join T_Sys_Users T On C.CorpUserGuid=T.UserGuid where 1=1 ";
            sql += (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            sql += "order by C.id desc";
            list = dbContext.Database.SqlQuery<Tb_Corp_CompanyInfo>(sql).ToList();
            return list;
        }

        /// <summary>
        /// 公共:查询企业审核信息列表
        /// </summary>
        /// <param name="pageCount"></param>
        /// <param name="pageSize"></param>
        /// <param name="conditions"></param>
        /// <returns></returns>
        public List<Tb_Corp_CompanyInfo1> GetFlowCorpInfos(int pageIndex, int pageSize, string condition, out int counts)
        {
            var list = new List<Tb_Corp_CompanyInfo1>();

            string sql = "SELECT distinct c.flowcode as TbFlowcode,c.CorpUserGuid,c.CorpName,C.LinkMan1,C.Tel,C.bCanBid, e.Year_Sorce as Score,e.TreeID,em.Name as PersonName,em.mobile as PersonMobile,q.DetailName as DetailName,q.GradeName as GradeName  From tb_Corp_CompanyInfo C left join T_Sys_Users T On C.CorpUserGuid=T.UserGuid  LEFT JOIN e_SorcePublish_2006 e  on t.DogID=e.TreeID AND e.bGrade in (105,99105) and e.bflag in (105,99105)" +
                " LEFT JOIN tb_Emp_Employee em on em.CorpUserGuid=c.CorpUserGuid AND em.typeflag=3 left JOIN tb_Corp_Quality q ON C.CorpUserGuid=q.CorpUserGuid AND q.KindID=132  AND q.IsDel =0 " +
                " where c.FlowCode <>'0'  and t.dogid>0  and  t.roleid in (105,99105) ";

            //string sql = "SELECT distinct c.flowcode as TbFlowcode,c.CorpUserGuid,c.CorpName,C.LinkMan1,C.Tel,C.bCanBid, "+
            //   " e.Year_Sorce as Score,e.TreeID,em.Name as PersonName,em.mobile as PersonMobile,q.DetailName as DetailName,q.GradeName as GradeName "+
            //   " From tb_Corp_CompanyInfo C left join T_Sys_Users T On C.CorpUserGuid=T.UserGuid  LEFT JOIN e_SorcePublish_2006 e  on t.DogID=e.TreeID " +
            //   " LEFT JOIN tb_Emp_Employee em on em.CorpUserGuid=c.CorpUserGuid left JOIN tb_Corp_Quality q ON C.CorpUserGuid=q.CorpUserGuid " +
            //   " where c.FlowCode <>'0'  and t.dogid>0  and  t.roleid in (105,99105) "+
            //   " AND e.bGrade in (105,99105) and e.bflag in (105,99105) AND em.typeflag=3  AND (q.IsMain=1 or q.KindID=132) AND q.IsDel =0 ";

            sql += (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            sql += " ORDER BY e. Year_Sorce desc";
            list = dbContext.Database.SqlQuery<Tb_Corp_CompanyInfo1>(sql).Distinct().ToList();
            var listpage = list.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            counts = list.Count;
            return listpage;
        }

        public List<Tb_Corp_CompanyInfo1> GetFlowCorpInfosSystem2(int pageIndex, int pageSize, string condition, out int counts)
        {
            var list = new List<Tb_Corp_CompanyInfo1>();
            string sql = @"SELECT Tb_FlowCode.flowcode as TbFlowcode,* From tb_Corp_CompanyInfo 
                             left join T_Sys_Users  On tb_Corp_CompanyInfo.CorpUserGuid=T_Sys_Users.UserGuid 
                             left join Tb_FlowCode  On CONVERT(nvarchar(50),tb_Corp_CompanyInfo.CorpUserGuid) =Tb_FlowCode.ObjectId
                             where Tb_FlowCode.CodeType = '8001' AND  Tb_FlowCode.FlowCode <>'0' ";
            sql += (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            sql += " order by tb_Corp_CompanyInfo.id desc";
            list = dbContext.Database.SqlQuery<Tb_Corp_CompanyInfo1>(sql).Distinct().ToList();
            var listpage = list.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            counts = list.Count;
            return listpage;
        }
        /// <summary>
        /// 查询企业个数
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int GetCorpInfosCount(int pageIndex, int pageSize, string condition)
        {
            int num = 0;

            string sql = "SELECT * From tb_Corp_CompanyInfo C left join T_Sys_Users T On C.CorpUserGuid=T.UserGuid where 1=1 ";
            sql += (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            sql += " order by C.id desc";
            num = dbContext.Database.SqlQuery<Tb_Corp_CompanyInfo>(sql).Count();
            return num;
        }

        /// <summary>
        /// 公共:查询企业详细信息
        /// </summary>
        /// <param name="conditions"></param>
        /// <returns></returns>
        public Tb_Corp_CompanyInfo GetCompanyInfo(string conditions)
        {
            string sql = "SELECT * From tb_Corp_CompanyInfo where 1=1 ";
            sql += (string.IsNullOrEmpty(conditions) ? string.Empty : conditions);
            Tb_Corp_CompanyInfo dbCorpComp = dbContext.Database.SqlQuery<Tb_Corp_CompanyInfo>(sql).FirstOrDefault();
            return dbCorpComp;
        }

        /// <summary>
        /// 公共:查询企业资质
        /// </summary>
        /// <param name="corpUserGuid"></param>
        /// <param name="conditions"></param>
        /// <returns></returns>
        public IList<tb_Corp_Quality> GetCompanyQualities(string corpUserGuid, string conditions)
        {
            var list = new List<tb_Corp_Quality>();
            string sql = String.Format("SELECT * From tb_Corp_Quality  where CorpUserGuid= '{0}' and (IsDel <> 1 or IsDel is null)  ", corpUserGuid);
            sql += (string.IsNullOrEmpty(conditions) ? string.Empty : conditions);
            sql += "order by id desc";
            list = dbContext.Database.SqlQuery<tb_Corp_Quality>(sql).ToList();
            return list;
        }

        /// <summary>
        /// 公共:根据企业组织机构代码查询企业占用情况
        /// CorpRegisterValidationModel类包含bool的是否已注册，如果已经注册则包含Tb_Corp_CompanyInfo信息，和string的说明信息。
        /// </summary>
        /// <param name="corpCode"></param>
        /// <returns></returns>
        public CorpRegisterValidationModel CorpRegisterValidate(string corpCode)
        {
            CorpRegisterValidationModel corpReg = new CorpRegisterValidationModel();
            Tb_Corp_CompanyInfo dbCorpComp = dbContext.Corp_CompanyInfos.FirstOrDefault(p => p.OrginazationCode == corpCode && (p.IsDel != true || p.IsDel == null));
            corpReg.CorpInfo = dbCorpComp;
            if (dbCorpComp != null)
            {
                corpReg.IsRegisted = true;
                corpReg.Message = "已注册";
            }
            else
            {
                corpReg.IsRegisted = false;
                corpReg.Message = "未注册";
            }
            return corpReg;
        }

        /// <summary>
        /// 公共:查询操作日志记录
        /// </summary>
        /// <param name="objectId"></param>
        /// <param name="codeType"></param>
        /// <returns></returns>
        public List<User_Log> GetUserLog(string objectId, string codeType)
        {
            string sql = "SELECT * FROM User_Log WHERE 1=1 ";
            if (!string.IsNullOrEmpty(objectId))
            {
                sql += String.Format(" and ObjectId = '{0}'", objectId);

            }
            if (!string.IsNullOrEmpty(codeType))
            {
                sql += String.Format(" and Code = '{0}'", codeType);

            }
            List<User_Log> logs = dbContext.Database.SqlQuery<User_Log>(sql).OrderBy(l => l.Id).ToList();

            return logs;
        }
        #endregion

        #region 企业月报统计相关
        /// <summary>
        /// 企业端获取企业产值、税金信息
        /// </summary>
        /// <param name="userGUID">企业GUID</param>
        /// <param name="year">年份</param>
        /// <param name="month">月份</param>
        /// <param name="flowCode">审核状态</param>
        /// <param name="corpName">公司名称</param>
        /// <returns></returns>
        public List<Tb_Corp_ProfitTotal_View> GetProfitList(string userGUID, int year, int month, string flowCode, string corpName)
        {
            StringBuilder sql = new StringBuilder("");
            sql.Append(" select p.*, ");
            sql.Append(" case when f.flowcode='0' then '未上报' ");
            sql.Append(" when f.flowcode='0,019' then '已上报' ");
            sql.Append(" when f.flowcode='0,019,021' then '退回'  ");
            sql.Append(" when f.flowcode='1,019,029' then '已审核'  ");
            sql.Append(" when f.flowcode='0,019,022' then '再次上报'  ");
            sql.Append(" else '未上报' end as FlowCode,  ");
            sql.Append(" f.Description  ");
            sql.Append(" from tb_Corp_ProfitTotal p ");
            sql.Append(" inner join t_sys_users u on p.corpuserguid = u.userguid ");
            sql.Append(" inner join tb_flowcode f on Convert(nvarchar(100),p.id) = f.objectID ");
            sql.Append(" where p.IsDel <> 1   and f.codetype=9060 ");
            sql.Append(" and u.roleID in (105,99105) ");
            sql.Append(" order by p.corpuserguid,p.iyear desc,p.imonth desc ");

            List<Tb_Corp_ProfitTotal_View> list = dbContext.Database.SqlQuery<Tb_Corp_ProfitTotal_View>(sql.ToString()).ToList();
            return list;
        }


        public List<Tb_Corp_ProfitTotalSum> GetSumProfitMoney()
        {
            StringBuilder sql = new StringBuilder("");
            sql.Append("  select  p.CorpUserGuid, ");
            sql.Append("  SUM(p.ProjectCount) as SUMProjectCount , ");
            sql.Append(" SUM(p.ZBMoney)  as SUMZBMoney , ");
            sql.Append("  SUM(p.ContractTotalMoney) as SUMContractTotalMoney , ");
            sql.Append("  SUM(p.ContractMoney) as SUMContractMoney	, ");
            sql.Append("  SUM(p.OtherContractMoney) as SUMOtherContractMoney ,  ");
            sql.Append("  SUM(p.TotalIncomeTax) as SUMTotalIncomeTax ,  ");
            sql.Append(" SUM(p.Turnover) as SUMTurnover ,  ");
            sql.Append("  SUM(p.OtherIncomeTax) as SUMOtherIncomeTax  , ");
            sql.Append("  SUM(p.WorkProfit) as SUMWorkProfit , ");
            sql.Append(" SUM(p.TaxTotal) as SUMTaxTotal  ");
            sql.Append(" from tb_Corp_ProfitTotal p ");
            sql.Append(" inner join t_sys_users u on p.corpuserguid = u.userguid ");
            sql.Append(" inner join tb_flowcode f on Convert(nvarchar(100),p.id) = f.objectID ");
            sql.Append(" where p.IsDel <> 1   and f.codetype=9060 ");
            sql.Append(" and u.roleID in (105,99105) ");
            //   sql.Append("  AND p.CorpUserGuid='" + CorpUserGuid + "' ");
            sql.Append("  AND p.iYear=" + DateTime.Now.Year + " AND f.FlowCode<>'0'");
            sql.Append("  GROUP BY p.CorpUserGuid ");

            List<Tb_Corp_ProfitTotalSum> profitlist = dbContext.Database.SqlQuery<Tb_Corp_ProfitTotalSum>(sql.ToString()).ToList();

            return profitlist;
        }

        public List<Tb_Corp_ProfitTotalGuid_View> GetProfitGuidList()
        {
            StringBuilder sql = new StringBuilder("");
            sql.Append(" select  p.CorpUserGuid,MAX(p.iYear) as iYear ,MAX(p.iMonth) as iMonth ,MAX(f.Id) as flowid ,MAX(f.UpdateTime) as updatetime ");
            sql.Append(" from tb_Corp_ProfitTotal p ");
            sql.Append(" inner join t_sys_users u on p.corpuserguid = u.userguid ");
            sql.Append(" inner join tb_flowcode f on Convert(nvarchar(100),p.id) = f.objectID ");
            sql.Append(" where p.IsDel <> 1   and f.codetype=9060  and f.FlowCode !='0' ");
            sql.Append(" and u.roleID in (105,99105) ");
            sql.Append(" GROUP BY p.CorpUserGuid ,p.iYear  ");
            sql.Append(" ORDER BY iYear desc,iMonth desc ");

            //sql.Append(" select  p.CorpUserGuid, p.iYear as iYear ,p.iMonth as iMonth ,f.Id as flowid ");
            //sql.Append(" from tb_Corp_ProfitTotal p ");
            //sql.Append(" inner join t_sys_users u on p.corpuserguid = u.userguid ");
            //sql.Append(" inner join tb_flowcode f on Convert(nvarchar(100),p.id) = f.objectID ");
            //sql.Append(" where p.IsDel <> 1   and f.codetype=9060 ");
            //sql.Append(" and u.roleID in (105,99105) ");
            //sql.Append(" ORDER BY f.FlowCode asc,iYear desc,iMonth desc ");

            List<Tb_Corp_ProfitTotalGuid_View> list = dbContext.Database.SqlQuery<Tb_Corp_ProfitTotalGuid_View>(sql.ToString()).ToList();
            return list;
        }

        public List<tb_Corp_ProfitTotal_GetMonthProfitTotalStat> GetProfitGuidListForManager(string corpName, int year, string areaCode)
        {
            string selectSql = " exec SP_tb_Corp_ProfitTotal_GetMonthProfitTotalStat @SumYear,@CorpName";
            SqlParameter[] parameters = {
                                            new SqlParameter("@SumYear", year.ToString()),
                                            new SqlParameter("@CorpName", corpName)
                                        };
            try
            {
                return dbContext.Database.SqlQuery<tb_Corp_ProfitTotal_GetMonthProfitTotalStat>(selectSql, parameters).ToList();
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 根据ID获取企业产值、税金信息
        /// </summary>
        /// <param name="profitID">产值、税金ID</param>
        /// <returns></returns>
        public Tb_Corp_ProfitTotal_View GetProfitByID(string profitID)
        {
            Tb_Corp_ProfitTotal_View profit = new Tb_Corp_ProfitTotal_View();
            StringBuilder sql = new StringBuilder("");
            sql.Append(" select p.*,f.flowcode,f.Description from tb_Corp_ProfitTotal p ");
            sql.Append(" inner join tb_flowcode f on Convert(nvarchar(100),p.id) = f.objectID ");
            sql.Append(" where p.IsDel <> 1   and f.codetype=9060 ");
            sql.Append(" and p.id= ").Append(profitID).Append("  ");

            profit = dbContext.Database.SqlQuery<Tb_Corp_ProfitTotal_View>(sql.ToString()).SingleOrDefault();
            return profit;
        }
        #endregion
    }
}
